-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<jiayuan yang>
-- Create date: <2013/01/27>
-- Description:	<Avaiable quantity for Purchase Order without specific storagein order>
-- =============================================
CREATE PROCEDURE POItemsBalanceEx
	-- Add the parameters for the stored procedure here
	(@PurchaseOrderID uniqueidentifier,
                   @StorageInID uniqueidentifier)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
 SELECT        D_PurchaseOrderItem.MainID AS PurchaseOrderID, PurchaseOrder.Code,PurchaseOrder.Vendor, P_Vendor.Name as VendorName,
                        D_PurchaseOrderItem.Material,P_Material.Name as MaterialName, D_PurchaseOrderItem.Measure,P_Measure.Name as MeasureName,
                        D_PurchaseOrderItem.Package, P_Package.Name  as PackageName,
                        D_PurchaseOrderItem.Quantity, D_PurchaseOrderItem.Price, D_PurchaseOrderItem.Amount, ISNULL(StorageIn.QuantityIn,0) as QuantityIn,
                         ISNULL(StorageIn.AmountIn,0) AS AmountIn, (Quantity - ISNULL(StorageIn.QuantityIn,0)) as QuantityBalance
FROM          
(Select * from D_PurchaseOrderItem Where D_PurchaseOrderItem.MainID = @PurchaseOrderID) as D_PurchaseOrderItem
INNER JOIN	(SELECT ID,Code,Vendor FROM D_PurchaseOrder Where CheckStatus =1) PurchaseOrder
ON D_PurchaseOrderItem.MainID = PurchaseOrder.ID INNER JOIN
                         P_Material ON D_PurchaseOrderItem.Material = P_Material.ID INNER JOIN
                         P_Measure ON D_PurchaseOrderItem.Measure = P_Measure.ID INNER JOIN
                         P_Package ON D_PurchaseOrderItem.Package = P_Package.ID INNER JOIN
                         P_Vendor ON PurchaseOrder.Vendor = P_Vendor.ID  
Left JOIN
                ( SELECT PurchaseOrder,Material,Package,Measure,
 SUM(Quantity) as QuantityIn, SUM(Amount) as AmountIn
 FROM D_StorageInDetail 
 Where PurchaseOrder IS NOT NULL 
 And MainID != @StorageInID
 Group by PurchaseOrder,Material,Package,Measure) AS StorageIn
                         ON D_PurchaseOrderItem.MainID = StorageIn.PurchaseOrder 
                         AND D_PurchaseOrderItem.Material = StorageIn.Material 
                         AND D_PurchaseOrderItem.Package = StorageIn.Package

END
GO
